﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Windows;
using System.Windows.Media.Imaging;

namespace Trade.Journal
{
    public static class DbHelper
    {
        private const string DBString = "DataSource=data.db";



        private static void CreateDatabase()
        {
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();

            db.Execute("CREATE TABLE IF NOT EXISTS AppData( Field STRING PRIMARY KEY, Value STRING )");
            db.Execute("CREATE TABLE IF NOT EXISTS Contacts( Symbol STRING PRIMARY KEY, Multiplicator INTEGER DEFAULT(0))");

            //记录主体
            db.Execute("CREATE TABLE IF NOT EXISTS Records ( Id STRING PRIMARY KEY, GroupId STRING NOT NULL, Contract STRING NOT NULL,  CoverPhoto BLOB, Version STRING, OpportunityCycle STRING,Opportunity STRING, ReleaseId STRING, CumulationId STRING,Score INTEGER,Date DATE, SaveTime DATETIME );");
            //释放
            db.Execute("CREATE TABLE IF NOT EXISTS Releases ( Id STRING PRIMARY KEY, Direction INTEGER, Level INTEGER, Scope DOUBLE, BarCount INTEGER, Score INTEGER );");
            //积累
            db.Execute("CREATE TABLE IF NOT EXISTS Cumulations ( Id STRING PRIMARY KEY, Type INTEGER,  Scope DOUBLE, BarCount INTEGER, Score INTEGER );");

            //评分
            //db.Execute("CREATE TABLE IF NOT EXISTS Scores ( RecordId STRING PRIMARY KEY, Total INTEGER, [Release] INTEGER, Cumulation INTEGER );");
            //每笔交易
            db.Execute("CREATE TABLE IF NOT EXISTS OpenDeals ( Id STRING PRIMARY KEY, RecordId STRING NOT NULL, Sequence INTEGER NOT NULL, Time DATETIME, Price DOUBLE, Lots DOUBLE, TradeType INTEGER, TradeCycle STRING,OpenDirection INTEGER, Score INTEGER );");
            db.Execute("CREATE TABLE IF NOT EXISTS CloseDeals ( Id STRING PRIMARY KEY, RecordId STRING NOT NULL , Sequence INTEGER NOT NULL, Time DATETIME, Price DOUBLE, Lots DOUBLE, TradeType INTEGER, TradeCycle STRING, CloseType INTEGER,Profit DOUBLE,Highest DOUBLE,Lowest DOUBLE Score INTEGER );");

            db.Execute("CREATE TABLE IF NOT EXISTS DealComments ( DealId STRING, Reason STRING) ");

            //图片
            db.Execute(@"CREATE TABLE IF NOT EXISTS Photos ( Id STRING PRIMARY KEY, RecordId STRING NOT NULL, DealId STRING NOT NULL,TargetCycle STRING, Data BLOB NOT NULL,Thumbnail BLOB ); ");
            //备注、心得、疑问、
            db.Execute(@"CREATE TABLE IF NOT EXISTS Remarks ( RecordId STRING NOT NULL,RemarkType INTEGER NOT NULL, Remark STRING NOT NULL ); ");
            //标签
            db.Execute(@"CREATE TABLE IF NOT EXISTS Marks ( RecordId STRING NOT NULL,Mark STRING NOT NULL ); ");
        }

        private static byte[] ImageSourceToBytes(BitmapSource source)
        {
            BitmapFrame frame = BitmapFrame.Create(source);
            var png = new JpegBitmapEncoder();
            png.Frames.Add(frame);

            using MemoryStream ms = new MemoryStream();

            png.Save(ms);
            ms.Position = 0;

            var by = ms.ToArray();// new byte[ms.Length];
            //ms.Write(by, 0, (int)ms.Length);

            return by;
        }

        private static BitmapSource BytesToImageSource(byte[] data)
        {
            var bmp = new BitmapImage();
            bmp.BeginInit();
            bmp.StreamSource = new MemoryStream(data);
            bmp.EndInit();
            return bmp;
        }


        public static IEnumerable<Record> LoadAll()
        {
            try
            {
                using SQLiteConnection db = new SQLiteConnection(DBString);
                db.Open();

                //return db.Query<string>("SELECT Id FROM Records").Select(x => LoadRecord(x));
                return db.Query<Record>("SELECT * FROM Records");

                return db.Query("SELECT * FROM Records").Select(x => new Record
                {
                    Contract = x.Contract,
                    Id = x.Id,
                    CoverPhoto = x.CoverPhotoId == null ? null : LoadPhotoInfo(x.CoverPhotoId),
                    Date = x.SaveTime.Date,
                    Version = Version.Parse(x.Version),
                    SaveTime = x.SaveTime
                });

            }
            catch (Exception er)
            {
                return null;
            }
        }


        //public static bool SaveToDb(NewRecordWindowViewModel v)
        //{
        //    using SQLiteConnection db = new SQLiteConnection(DBString);
        //    db.Open();
        //    var trans = db.BeginTransaction();
        //    try
        //    {
        //        //记录主体
        //        var cpid = v.CoverPhoto?.Id;
        //        if (string.IsNullOrWhiteSpace(cpid))
        //            cpid = v.PhotoInfos.FirstOrDefault()?.Id;
        //        db.Execute("INSERT INTO Records VALUES(@Id,@Contract,@CoverPhoto,@Version,@OpportunityCycle,@ReleaseDirection,@ReleaseLevel,@ReleaseScope,@ReleaseBarCount,@CumulationType,@CumulationScope,@CumulationBarCount,@SaveTime)", new
        //        {
        //            Id = v.RecordId,
        //            Contract = v.Contract,
        //            CoverPhoto = v.CoverPhoto?.Thumbnail,
        //            Version = Assembly.GetCallingAssembly().GetName().Version.ToString(),
        //            OpportunityCycle = v.OpportunityCycle.ToString(),
        //            ReleaseDirection = v.ReleaseDirection,
        //            ReleaseLevel = v.ReleaseLevel,
        //            ReleaseScope = v.ReleaseScope,
        //            ReleaseBarCount = v.ReleaseBarCount,
        //            CumulationType = v.CumulationType,
        //            CumulationScope = v.CumulationScope,
        //            CumulationBarCount = v.CumulationBarCount,
        //            SaveTime = DateTime.Now
        //        });
        //        //评分
        //        db.Execute("INSERT INTO Scores VALUES(@RecordId,@Total,@Release,@Cumulation)", new { RecordId = v.RecordId, Total = v.Scores.Full, Release = v.Scores.Release, Cumulation = v.Scores.Cumulation });

        //        //记录入场
        //        db.Execute("INSERT INTO Deals  VALUES(@DealId,@RecordId,@Sequence,@Price,@Lots)", new { DealId = v.OpenDeal.DealId, RecordId = v.RecordId, Sequence = 0, Price = v.OpenDeal.Price, Lots = v.OpenDeal.Lots });
        //        //记录减仓、出场
        //        foreach (var item in v.DealRecords)
        //        {
        //            db.Execute("INSERT INTO Deals VALUES(@DealId,@RecordId,@Sequence,@Price,@Lots)", new { DealId = item.DealId, RecordId = item.RecordId, Sequence = item.Sequence, Price = item.Price, Lots = item.Lots });
        //        }
        //        //记录说明
        //        foreach (var item in v.Remarks)
        //        {
        //            db.Execute("INSERT INTO Remarks VALUES(@RecordId,@RemarkType,@Remark)", new { RecordId = v.RecordId, RemarkType = RemarkType.Lesson, Remark = item });
        //        }
        //        foreach (var item in v.Questions)
        //        {
        //            db.Execute("INSERT INTO Remarks VALUES(@RecordId,@RemarkType,@Remark)", new { RecordId = v.RecordId, RemarkType = RemarkType.Question, Remark = item });
        //        }
        //        //标签
        //        foreach (var item in v.Marks)
        //        {
        //            db.Execute("INSERT INTO Marks VALUES(@RecordId,@Mark )", new { RecordId = v.RecordId, Mark = item });
        //        }

        //        foreach (var item in v.PhotoInfos)
        //        {
        //            if (item.Data != null && !string.IsNullOrWhiteSpace(item.RecordId) && !string.IsNullOrWhiteSpace(item.DealId))
        //                db.Execute("INSERT INTO Photos VALUES(@Id,@RecordId,@DealId,@Data,@Thumbnail)", new
        //                {
        //                    Id = item.Id,
        //                    RecordId = item.RecordId,
        //                    DealId = item.DealId,
        //                    Data = ImageSourceToBytes(item.Data),
        //                    Thumbnail = ImageSourceToBytes(item.Thumbnail)
        //                });
        //        }
        //        if (v.CoverPhoto != null && !v.PhotoInfos.Contains(v.CoverPhoto) && v.CoverPhoto.Data != null)
        //            db.Execute("INSERT INTO Photos VALUES(@Id,@RecordId,@DealId,@Data)", new
        //            {
        //                Id = v.CoverPhoto.Id,
        //                RecordId = v.CoverPhoto.RecordId,
        //                DealId = v.CoverPhoto.DealId,
        //                Data = ImageSourceToBytes(v.CoverPhoto.Data),
        //                Thumbnail = ImageSourceToBytes(v.CoverPhoto.Thumbnail)
        //            });
        //        trans.Commit();
        //        return true;
        //    }
        //    catch (Exception er)
        //    {
        //        trans.Rollback();
        //        MessageBox.Show($"保存错误，{er.Message}");
        //        return false;
        //    }

        //}

        public static bool SaveRecord(Record v)
        {
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();
            var trans = db.BeginTransaction();

            try
            {
                if (db.QueryFirstOrDefault<int>($"SELECT COUNT(*) FROM Contacts WHERE Symbol='{v.Contract}'") > 0)
                    db.Execute($"UPDATE Contacts SET Multiplicator={v.Multiplicator} WHERE Symbol='{v.Contract}' ");
                else
                    db.Execute("INSERT INTO Contacts VALUES(@Symbol,@Multiplicator)", new { Symbol = v.Contract, Multiplicator = v.Multiplicator });

                //记录主体 
                db.Execute("INSERT INTO Records VALUES(@Id,@GroupId,@Contract,@CoverPhoto,@Version,@OpportunityCycle,@Opportunity,@ReleaseId,@CumulationId,@Score,@Date,@SaveTime)", new
                {
                    v.Id,
                    v.GroupId,
                    v.Contract,
                    v.CoverPhoto,
                    Version = Assembly.GetCallingAssembly().GetName().Version.ToString(),
                    OpportunityCycle = v.OpportunityCycle,
                    v.Opportunity,
                    ReleaseId = v.Release?.Id,
                    CumulationId = v.Cumulation?.Id,
                    v.Score,
                    v.Date,
                    SaveTime = DateTime.Now
                });
                //评分
                //db.Execute("INSERT INTO Scores VALUES(@RecordId,@Total,@Release,@Cumulation)", new { RecordId = v.RecordId, Total = v.Scores.Full, Release = v.Scores.Release, Cumulation = v.Scores.Cumulation });
                //释放
                if (v.Release != null)
                    db.Execute("INSERT INTO Releases VALUES(@Id,@Direction,@Level,@Scope,@BarCount,@Score);", new { Id = v.Release.Id, v.Release.Direction, v.Release.Level, v.Release.Scope, v.Release.BarCount, v.Release.Score });
                //积累
                if (v.Cumulation != null)
                    db.Execute("INSERT INTO Cumulations VALUES(@Id,@Type,@Scope,@BarCount,@Score);", new { v.Cumulation.Id, v.Cumulation.Type, v.Cumulation.Scope, v.Cumulation.BarCount, v.Cumulation.Score });

                //记录入场
                if (v.OpenDeal != null)
                {
                    db.Execute("INSERT INTO OpenDeals VALUES(@Id , @RecordId , @Sequence , @Time , @Price , @Lots , @TradeType , @TradeCycle ,@OpenDirection , @Score)", new
                    {
                        v.OpenDeal.Id,
                        RecordId = v.Id,
                        Sequence = 0,
                        v.OpenDeal.Time,
                        v.OpenDeal.Price,
                        v.OpenDeal.Lots,
                        v.OpenDeal.TradeType,
                        TradeCycle = v.OpenDeal.TradeCycle,
                        v.OpenDeal.OpenDirection,
                        v.OpenDeal.Score
                    });
                    foreach (var item in v.OpenDeal.Reasons)
                        db.Execute("INSERT INTO DealComments VALUES(@DealId, @Reason)", new { DealId = v.OpenDeal.Id, Reason = item });
                    foreach (var item in v.OpenDeal.Photos)
                    {
                        if (item.Data != null && !string.IsNullOrWhiteSpace(item.RecordId) && !string.IsNullOrWhiteSpace(item.DealId))
                            db.Execute("INSERT INTO Photos VALUES(@Id,@RecordId,@DealId,@TargetCycle,@Data,@Thumbnail)", item);

                    }
                }

                //记录减仓、出场
                foreach (var item in v.CloseDeals)
                {
                    db.Execute("INSERT INTO CloseDeals VALUES(@Id , @RecordId , @Sequence , @Time , @Price , @Lots , @TradeType , @TradeCycle ,@Profit ,@Highest,@Lowest, @Score)", new
                    {
                        Id = item.Id,
                        RecordId = v.Id,
                        item.Sequence,
                        item.Time,
                        item.Price,
                        item.Lots,
                        item.TradeType,
                        TradeCycle = item.TradeCycle.ToString(),
                        item.CloseType,
                        item.Profit,
                        item.Highest,
                        item.Lowest,
                        item.Score
                    });
                    foreach (var re in item.Reasons)
                        db.Execute("INSERT INTO DealComments VALUES(@DealId, @Reason)", new { DealId = item.Id, Reason = re });
                    foreach (var photo in item.Photos)
                    {
                        if (photo.Data != null && !string.IsNullOrWhiteSpace(photo.RecordId) && !string.IsNullOrWhiteSpace(photo.DealId))
                            db.Execute("INSERT INTO Photos VALUES(@Id,@RecordId,@DealId,@TargetCycle,@Data,@Thumbnail)", photo);

                    }
                }


                //记录说明
                foreach (var item in v.Lessons)
                {
                    db.Execute("INSERT INTO Remarks VALUES(@RecordId,@RemarkType,@Remark)", new { RecordId = v.Id, RemarkType = RemarkType.Lesson, Remark = item });
                }
                foreach (var item in v.Questions)
                {
                    db.Execute("INSERT INTO Remarks VALUES(@RecordId,@RemarkType,@Remark)", new { RecordId = v.Id, RemarkType = RemarkType.Question, Remark = item });
                }
                //标签
                foreach (var item in v.Marks)
                {
                    db.Execute("INSERT INTO Marks VALUES(@RecordId,@Mark )", new { RecordId = v.Id, Mark = item });
                }

                trans.Commit();
                return true;
            }
            catch (Exception er)
            {
                trans.Rollback();
                MessageBox.Show($"保存错误，{er.Message}");
                return false;
            }

        }

        static DbHelper()
        {
            SqlMapper.AddTypeHandler(typeof(BarCycle), new BarCycleTypeHandler());
            SqlMapper.AddTypeHandler(typeof(Version), new VersonTypeHandler());
            SqlMapper.AddTypeHandler(typeof(BitmapSource), new BitmapSourceTypeHandler());
            SqlMapper.AddTypeHandler(typeof(Opportunity), new OpportunityTypeHandler());

            CreateDatabase();

            Migrate();
        }

        private static void Migrate()
        {
            var data = Assembly.GetExecutingAssembly().GetCustomAttributes(typeof(DatabaseVersion), false);
            if (data.FirstOrDefault() is DatabaseVersion v)
            {
                var NessisaryVer = Version.Parse(v.Version);
                var CurrentVer = GetConfigValue<Version>("DataVersion");

                if (CurrentVer >= NessisaryVer)
                    return;

                using SQLiteConnection db = new SQLiteConnection(DBString);
                db.Open();

                var info = Application.GetResourceStream(new Uri("DbMigrate.txt", UriKind.Relative));
                if (info.Stream != null && info.Stream.CanRead)
                {
                    try
                    {
                        using (var sr = new StreamReader(info.Stream))
                        {
                            while (!sr.EndOfStream)
                            {
                                var head = sr.ReadLine();
                                if (head.StartsWith("#Version="))
                                {
                                    if (Version.TryParse(head.Substring(9), out Version ve))
                                    {
                                        if (ve > CurrentVer && ve <= NessisaryVer)
                                        {
                                            var trans = db.BeginTransaction();
                                            string sql = "";
                                            while (!sr.EndOfStream)
                                            {
                                                var str = sr.ReadLine();
                                                if (str.Trim() == "###" || string.IsNullOrWhiteSpace(str))
                                                {
                                                    db.Execute(sql);
                                                    SetConfigValue("DataVersion", ve, db);
                                                    trans.Commit();
                                                    break;
                                                }
                                                else
                                                {
                                                    sql += str;
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                    catch (Exception er)
                    {
                        throw;
                    }
                }
            }
        }

        public static T GetConfigValue<T>(string field)
        {
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();
            return db.QuerySingleOrDefault<T>($"SELECT Value FROM AppData WHERE Field='{field}'");
        }

        public static void SetConfigValue<T>(string field, T value)
        {
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();
            if (value is bool)
                db.Execute("REPLACE INTO AppData Values(@f,@v)", new { v = value.ToString(), f = field });
            else
                db.Execute("REPLACE INTO AppData Values(@f,@v)", new { v = value, f = field });
        }
        public static void SetConfigValue<T>(string field, T value, SQLiteConnection db)
        {
            db.Execute("REPLACE INTO AppData Values(@f,@v)", new { v = value, f = field });
        }
        public static int QueryMultiplicator(string contract)
        {
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();

            return db.QuerySingleOrDefault<int>($"SELECT Multiplicator FROM Contacts WHERE Symbol='{contract}'");
        }

        public static bool Update(this Record record, object v, [CallerMemberName]string sqlfield = null)
        {
            if (record == null) return false;
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();
            try
            {
                if (string.IsNullOrWhiteSpace(sqlfield))
                    return false;

                db.Execute($"UPDATE Records SET {sqlfield}=@v WHERE Id='{record.Id}'", new { v });
                return true;
            }
            catch (Exception er)
            {
                return false;
            }
        }
        public static bool Update(this CumulationInfo cum, object v, [CallerMemberName]string sqlfield = null)
        {
            if (cum == null) return false;
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();
            try
            {
                if (string.IsNullOrWhiteSpace(sqlfield))
                    return false;

                db.Execute($"UPDATE Cumulations SET {sqlfield}=@v WHERE Id='{cum.Id}'", new { v });
                return true;
            }
            catch (Exception er)
            {
                return false;
            }
        }
        public static bool Update(this ReleaseInfo rel, object v, [CallerMemberName]string sqlfield = null)
        {
            if (rel == null) return false;
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();
            try
            {
                if (string.IsNullOrWhiteSpace(sqlfield))
                    return false;

                db.Execute($"UPDATE Releases SET {sqlfield}=@v WHERE Id='{rel.Id}'", new { v });
                return true;
            }
            catch (Exception er)
            {
                return false;
            }
        }
        public static bool Update(this DealBase deal, object v, [CallerMemberName]string sqlfield = null)
        {
            if (deal == null) return false;
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();
            try
            {
                if (string.IsNullOrWhiteSpace(sqlfield))
                    return false;

                db.Execute($"UPDATE {deal.GetType().Name} SET {sqlfield}=@v WHERE Id='{deal.Id}'", new { v });
                return true;
            }
            catch (Exception er)
            {
                return false;
            }
        }
        public static bool Update(this DealViewModel deal, object v, [CallerMemberName]string sqlfield = null)
        {
            if (deal == null) return false;
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();
            try
            {
                if (string.IsNullOrWhiteSpace(sqlfield))
                    return false;

                if (deal is OpenDealViewModel)
                    db.Execute($"UPDATE OpenDeals SET {sqlfield}=@v WHERE Id='{deal.DealId}'", new { v });
                else if (deal is UnderweightDealViewModel)
                    db.Execute($"UPDATE CloseDeals SET {sqlfield}=@v WHERE Id='{deal.DealId}'", new { v });
                return true;
            }
            catch (Exception er)
            {
                return false;
            }
        }

        public static PhotoInfo LoadPhotoInfo(string id)
        {
            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();

            var obj = db.QueryFirstOrDefault($"SELECT * FROM Photos WHERE Id='{id}'");

            return new PhotoInfo
            {
                Id = obj.Id,
                RecordId = obj.RecordId,
                DealId = obj.DealId,
                Data = BytesToImageSource(obj.Data),
                Thumbnail = BytesToImageSource(obj.Thumbnail)
            };
        }

        public static Record LoadRecord(string id, bool simple = true)
        {
            if (string.IsNullOrWhiteSpace(id))
                return null;

            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();

            var record = db.QuerySingleOrDefault<Record>($"SELECT * FROM Records WHERE Id = '{id}'");
            //Record record = new Record();
            //var data = db.QuerySingleOrDefault($"SELECT * FROM Records WHERE Id = '{id}'");

            //record.Id = data.Id;
            //record.Contract = data.Contract;
            //if (data.CoverPhotoId != null)
            //    record.CoverPhoto = LoadPhotoInfo(data.CoverPhotoId);

            //record.Version = Version.Parse(data.Version);
            //record.OpportunityCycle = data.OpportunityCycle;
            //record.Score = data.Score;

            if (simple)
                return record;

            FillRecord(record);
            return record;
        }

        public static void FillRecord(Record record)
        {
            if (record == null)
                return;

            using SQLiteConnection db = new SQLiteConnection(DBString);
            db.Open();

            record.Release = db.QuerySingleOrDefault<ReleaseInfo>($"SELECT * FROM Releases WHERE Id=(SELECT ReleaseId FROM Records WHERE Id='{record.Id}')");
            record.Cumulation = db.QuerySingleOrDefault<CumulationInfo>($"SELECT * FROM Cumulations WHERE Id=(SELECT CumulationId FROM Records WHERE Id='{record.Id}')");
            record.OpenDeal = db.QuerySingleOrDefault<OpenDeal>($"SELECT * FROM OpenDeals WHERE RecordId='{record.Id}'");
            record.OpenDeal.Photos = db.Query<PhotoInfo>($"SELECT * FROM Photos WHERE DealId='{record.OpenDeal.Id}'").ToList();
            record.CloseDeals = db.Query<CloseDeal>($"SELECT * FROM CloseDeals WHERE RecordId='{record.Id}'").ToList();
            foreach (var item in record.CloseDeals)
            {
                item.Photos = db.Query<PhotoInfo>($"SELECT * FROM Photos WHERE DealId='{item.Id}'").ToList();
            }

            var rm = db.Query($"SELECT * FROM Remarks WHERE RecordId='{record.Id}'");
            record.Lessons = rm.Where(x => x.RemarkType == (long)RemarkType.Lesson).Select(x => x.Remark as string).ToList();
            record.Questions = rm.Where(x => x.RemarkType == (long)RemarkType.Question).Select(x => x.Remark as string).ToList();
            record.Marks = db.Query<string>($"SELECT Mark FROM Marks WHERE RecordId='{record.Id}'").ToList();
        }
    }
}
